PostgreSQL 15にMERGE文UPSERTがやってくる

PostgreSQL 15にMERGE文UPSERTがやってくる

PostgreSQL 15がMERGE文を使ったUPSERTに対応します
#PostgreSQL
#SQL
quiver

quiver

facebook logohatena logotwitter logo
Clock Icon2022.06.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

UPSERT とは

データベースにレコードを反映する際、100%新規データを扱うのであれば、テーブルにINSERTするだけですみます。 更新データも存在する場合、新規の場合は INSERT 、更新の場合は UPDATE というように処理を振り分ける必要があります。 このように、データの状態によって UPDATE と INSERT を使い分けることを、UPDATE と INSERT をくっつけて UPSERT と呼びます。

PostgreSQL は9.5で INSERT を試し、制約違反が発生すれば UPDATE する INSERT ... ON CONFLICT 構文の UPSERT に対応しました。 PostgreSQL 以外にも、MySQLやSQLiteがこの方式のUPSERTに対応しています。

INSERT INTO target(tid, val)
   VALUES(123, 10)
   ON CONFLICT (tid)
DO UPDATE SET balance = EXCLUDED.balance;

次期リリースの PostgreSQL 15 からは、MERGE 文による UPSERT にも対応します。

MERGE INTO target AS t
USING (VALUES (123, 10)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED THEN
    UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
    INSERT (tid, balance) VALUES (i.tid, i.balance);

MERGE 構文

MERGE 文はSQL の標準化を進める SQL:2003 で導入され、Oracle、SQL Server、BigQuery などが対応しています。

PostgreSQL の MERGE 構文は以下の通りです。

[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

where data_source is

{ source_table_name | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is

DELETE

標準SQLに準拠していますが、 WITH と DO NOTHING は PostgreSQL の独自拡張です。

やってみた

この MERGE 文を使ったユースケースを2パターン紹介します。

検証環境

PostgreSQL 15 beta1 を利用しました。

foo=# SELECT version();
                                                                 version
------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta1 (Debian 15~beta1-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

単一テーブルへの UPSERT

単一のテーブルに対して、プライマリーキーの有無に応じて、 INSERT/UPDATE を切り替えるシンプルな UPSERT です。

まず、key/valueのシンプルな構造のテーブルを用意します。

CREATE TABLE target (
    id int8 generated always as identity,
    tid int4 not null unique,
    balance int4 not null default 0,
    primary key (id)
);

中身は空っぽです。

foo=# SELECT * FROM target;
 id | tid | balance
----+-----+---------
(0 rows)

このテーブルに、次のレコードをMERGE文で追加します。

  • tid : 123
  • balance : 10
MERGE INTO target AS t
USING (values (123, 10)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED THEN
    UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
    INSERT (tid, balance) VALUES (i.tid, i.balance);

テーブルは空っぽなので、 INSERT されました。

foo=# SELECT * FROM target;
 id | tid | balance
----+-----+---------
  1 | 123 |      10
(1 row)

同じキー(tid) のレコードをもう一度 MERGE します。

MERGE INTO target AS t
USING (VALUES (123, 30)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED THEN
    UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
    INSERT (tid, balance) VALUES (i.tid, i.balance);

balance が新しい値になっていることから、UPDATE されたことがわかります。

foo=# SELECT * FROM target;
 id | tid | balance
----+-----+---------
  1 | 123 |      30
(1 row)

同じSQLなのに、レコードの状態によって 処理が INSERT から UPDATE へと変わっています。これが UPSERT です。

複数テーブル間の UPSERT

2テーブル間のデータの状態にに応じて、 INSERT/UPDATE などの処理を切り替えるより複雑な UPSERT です。

先程と同じターゲットテーブルを流用し、次のソーステーブルを追加します。

CREATE TABLE source (
    id int8 generated always as identity,
    sid int4 not null unique,
    delta int4 not null default 0,
    primary key (id)
);

ソーステーブル(source)のレコードをターゲットテーブル(target)にMERGEします。

ソース・ターゲットテーブルには、以下のレコードがあるものとします。

SELECT s.sid, t.balance, s.delta
FROM source s
LEFT JOIN target t
ON s.sid = t.tid
ORDER BY s.sid;

 sid | balance | delta
-----+---------+-------
 123 |      30 |     5
 456 |      10 |   100
 789 |         |    10
(3 rows)

この状態で、先程よりも MATCH 条件を複雑にし

  • INSERT
  • UPDATE
  • DELETE
  • DO NOTHING:処理なし

の4パターンの処理が可能な MERGE を実行します。

MERGE INTO target AS t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED AND t.balance > s.delta THEN
  UPDATE SET balance = t.balance - s.delta
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED AND s.delta > 0 THEN
  INSERT (tid, balance) VALUES (s.sid, s.delta)
WHEN NOT MATCHED THEN
  DO NOTHING;

マッチ条件に応じた処理が行われています。

foo=# select * from target;
 id | tid | balance
----+-----+---------
  2 | 123 |      25
  5 | 789 |      10
(2 rows)

MERGEが発火するトリガー

行・文トリガーが設定されたテーブルに MERGE を実行した場合の仕様は以下の通りです。

statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. In contrast, row-level triggers will fire only for the specific event type being executed. So a MERGE command might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.

https://www.postgresql.org/docs/15/sql-merge.html

具体例で考えます。

{行, 文} X {INSERT,UPDATE} の4パターンのトリガーが target テーブルに設定されていたとします。

次の MERGE SQL を実行したとします。

MERGE INTO target AS t
USING (values (1, 1), (2, 2)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED THEN
    UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
    INSERT (tid, balance) VALUES (i.tid, i.balance);

このMERGE 文には INSERT 文も UPDATE 文も含まれるため、MATCH 条件結果に関係なく、INSERTとUPDATEの文トリガーが1回ずつ発火されます。

一方で、行トリガーについては、MATCH 条件結果に応じて対応する INSERTまたはUPDATEの行トリガーが発火されます。

例えば、マージしようとした2レコードがともに INSERT された場合、INSERT の行トリガーが2回発火されます。

以上をまとめると、文・行トリガーの呼び出し回数は次の表の通りです。

トリガー INSERT UPDATE
文 1 1
行 2 0

また、次の SQL のように、MERGE 文に複数の UPDATE 文がある場合、発火される UPDATE 文トリガーは2回ではなく1回です。

MERGE INTO TARGET AS t
USING (values (1, 1), (2,2)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED AND SOME_CONDITION THEN
    UPDATE SET balance = i.balance
WHEN MATCHED THEN
    UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
    INSERT (tid, balance) VALUES (i.tid, i.balance);

INSERT ... ON CONFLICT を使った UPSERT

今回紹介した MERGE 文以外にも、PostgreSQL 9.5 以降であれば INSERT ... ON CONFLICT 構文で UPSERT できます。

INSERT 時にコンフリクトが発生すると、DO フローに突入します。

INSERT INTO target(tid, val)
   VALUES(123, 10)
   ON CONFLICT (tid)
DO UPDATE SET balance = EXCLUDED.balance;

当然のことながら、コンフリクトが発生しないと UPDATE は起こりません。 テーブルには適切に制約を設定してください。

MERGE と INSERT ON CONFLICT の同時実行性の違い

MERGE と INSERT ... ON CONFLICT は同時に挿入される場合の振る舞いが大きく異なり、INSERT ... ON CONFLICT の方が安全です。

You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs.

https://www.postgresql.org/docs/15/sql-merge.html

この動作を確認します。

2つのトランザクションが同じキーの INSERT を同時に実行し、UPSERT の INSERT が後に実行されるシナリオを考えます。

まずは MERGE の場合です。

TX A TX B
BEGIN
BEGIN
INSERT INTO target(tid, balance)
VALUES(1, 1);
行排他ロック獲得
MERGE INTO target AS t
USING (VALUES (1, 10)) AS i(tid, balance)
ON t.tid = i.tid
WHEN MATCHED THEN
UPDATE SET balance = i.balance
WHEN NOT MATCHED THEN
INSERT (tid, balance) VALUES (i.tid, i.balance);
共有ロック の granted : false
TX Aの終了待ち
COMMIT
ERROR: duplicate key value violates unique constraint "target_tid_key"
DETAIL: Key (tid)=(1) already exists.

トランザクション開始時のスナップショットをもとに MERGE は INSERT しようとし、TX Aコミット後にいざ INSERT すると、同じキーのレコードが存在するため、制約違反が発生します。

INSERT ... ON CONFLICT 方式であれば、エラーは発生しません。

TX A TX B
BEGIN
BEGIN
INSERT INTO target(tid, balance)
VALUES(2, 2);
行排他ロック獲得
INSERT INTO target(tid, balance)
VALUES(2, 20)
ON CONFLICT(tid)
DO
UPDATE SET balance = EXCLUDED.balance;
共有ロック の granted : false
TX Aの終了待ち
COMMIT
INSERT 0 1

実行結果には INSERT 0 1 とありますが、実際には ISNERT 時にコンフリクトが発生し、UPDATE されています。

MERGE と INSERT ... ON CONFLICT は常に置き換え可能ではないことに、ご注意ください。

最後に

PostgreSQL は従来から INSERT ... ON CONFLICT 構文の UPSERT に対応していましたが、バージョン 15 からは MERGE 文 UPSERT にも対応します。

Big Query、Oracle、Microsoft SQL Server など主要RDBMSがサポートしてきた標準SQLの一つであり、記述が複雑になりがちな UPSERT 処理を見通しよく記述できます。

PostgreSQLの公式ドキュメントには、MERGE にまつわる

  • トリガー
  • JOINした候補が複数ある場合
  • 到達できない WHEN (NOT) MATCHED 句が存在するとシンタックスエラーが発生(ERROR: unreachable WHEN clause specified after unconditional WHEN clause)

など、様々な注意事項が記載されています。 ぜひご一読ください。

それでは。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

MySQLの暗黙的コミットについて調べて実際に試してみた

MySQLの暗黙的コミットについて調べて実際に試してみた

User avatar
sora
2024.10.29
テクサポと学ぶ PostgreSQL #5 psql

テクサポと学ぶ PostgreSQL #5 psql

User avatar
村上紗敏
2024.10.29
[アップデート] Amazon Aurora PostgreSQL でも遂にリードレプリカのローカル書き込み転送機能がサポートされました

[アップデート] Amazon Aurora PostgreSQL でも遂にリードレプリカのローカル書き込み転送機能がサポートされました

User avatar
いわさ
2024.10.18
EC2 Instance Connect Endpoint 経由で RDS for PostgreSQL に接続する構成を AWS CDK で構築してみた

EC2 Instance Connect Endpoint 経由で RDS for PostgreSQL に接続する構成を AWS CDK で構築してみた

User avatar
若槻龍太
2024.10.02
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.